1.13 MySQL bin-log

1. What Is bin-log

• A log at the MySQL Server layer, used for log archiving.
• Records change operations on the database, excluding query operations.

2. Why bin-log Is Still Needed When There Is redo-log

• redo-log belongs to the InnoDB storage engine, while bin-log is at the MySQL Server layer.

3. Functions of bin-log

3.1. Backup and Recovery

1. Generally, the complete MySQL database and bin-log are both backed up periodically.
2. Find the most recent full backup of the complete database.
3. Starting from the backup time point, retrieve the backed-up binlogs in sequence and replay them.

3.2. Primary-Replica Replication

• MySQL Primary-Replica Replication.md

4. bin-log File Location

• By default, it is placed in the data directory.
• Naming format: mysql-bin.000001

5. bin-log Formats

5.1. statement Format

• Records the original SQL statements, such as insert/delete/update.
• Problem:
  • It may cause inconsistency between primary and replica. For example, with current_time, the time on the primary is time A, while when it reaches the replica it is time B.

5.2. row Format

• Records which record was modified and what the values were before and after the modification.
  • For row insertion, the log records the new values of the related columns.
  • For row deletion, the log marks that this row was deleted.
  • For row update, the log records the new values of all columns.
• Problem:
  • It takes up a lot of space. For example, when deleting 10,000 rows, with statement only delete from t where id in (xxx) needs to be recorded, while with row 10,000 deleted rows need to be recorded.

5.3. mixed Format

• A mixture of the two formats above. MySQL uses row for operations that may cause primary-replica inconsistency; otherwise it uses statement.

5.4. Choosing a bin-log Format

• row is used in most cases because it records complete information and can be used for data recovery.

6. bin-log Write Mechanism

• 
• During transaction execution, logs are first written to the binlog cache. When the transaction is committed, the binlog cache is written to the binlog file.
• The timing of write and fsync is controlled by the sync_binlog parameter:
  • When sync_binlog=0, each transaction commit only performs write, not fsync.
  • When sync_binlog=1, each transaction commit performs write + fsync.
    • This is generally used for safety.
  • When sync_binlog=N (N>1), each transaction commit performs write, but fsync is performed only after N transactions have accumulated.

7. References

• 44. MySQL binlog Parsing - bilibili